﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using System.Collections;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using Agresso.Interface.Layer.Domain;
using System.Data;

namespace Agresso.Interface.Layer.Service
{
    public class VoyageMasterService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        VesselService m_vesselService;
        ProfitCentreService m_profitCentreService;
        ud_voyageimp_Service m_ud_voyageimp_Service;
        AlertService m_alertService;


        public VoyageMasterService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_profitCentreService = new ProfitCentreService();
            m_vesselService = new VesselService();
            m_ud_voyageimp_Service = new ud_voyageimp_Service();
            m_alertService = new AlertService();
        }
        public void ImportAllDataSoftmarToMiddleLayer()
        {
            try
            {
                IList vms = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = VoyageMasterSQL.GetAllVoyageMaster();
                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    VoyageMaster po = new VoyageMaster(rd, false);
                    vms.Add(po);
                }
                rd.Close();
                foreach (VoyageMaster vm in vms)
                {
                    if (vm.voyage_no.ToString().Length >= 8) continue;// example : 20130019
                    cmd.CommandText = VoyageMasterSQL.CountVoyageMasterTemp();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vm.vessel_name;
                    cmd.Parameters.Add("@dept_fullname", SqlDbType.NVarChar).Value = vm.dept_fullname;
                    cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = vm.voyage_no;
                    cmd.Parameters.Add("@charterer", SqlDbType.NVarChar).Value = vm.charterer;
                    cmd.Parameters.Add("@head_fixture_type", SqlDbType.NVarChar).Value = vm.head_fixture_type;
                    cmd.Parameters.Add("@cp_date", SqlDbType.DateTime).Value = vm.cp_date;
                    cmd.Parameters.Add("@estimated_start_date", SqlDbType.DateTime).Value = vm.estimated_start_date;
                    cmd.Parameters.Add("@start_ballast_date", SqlDbType.DateTime).Value = vm.start_ballast_date;
                    cmd.Parameters.Add("@start_voyage_delivery_date", SqlDbType.DateTime).Value = vm.start_voyage_delivery_date;
                    cmd.Parameters.Add("@end_voyage_delivery_date", SqlDbType.DateTime).Value = vm.end_voyage_delivery_date;
                    cmd.Parameters.Add("@days_total_on_hire", SqlDbType.Decimal).Value = vm.days_total_on_hire;
                    cmd.Parameters.Add("@days_total_off_hire", SqlDbType.Decimal).Value = vm.days_total_off_hire;
                    cmd.Parameters.Add("@tce", SqlDbType.Decimal).Value = vm.tce;
                    cmd.Parameters.Add("@commodity_type", SqlDbType.NVarChar).Value = vm.commodity_type;
                    cmd.Parameters.Add("@cargo_quantity", SqlDbType.Decimal).Value = vm.cargo_quantity;
                    cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = vm.status;
                    cmd.Parameters.Add("@head_fixture_number", SqlDbType.Int).Value = vm.head_fixture_number;
                    cmd.Parameters.Add("@fixture_ref", SqlDbType.NVarChar).Value = vm.fixture_ref;
                    cmd.Parameters.Add("@head_fixture_ref", SqlDbType.NVarChar).Value = vm.head_fixture_ref;
                    cmd.Parameters.Add("@freight_rate_basis", SqlDbType.NVarChar).Value = vm.freight_rate_basis;
                    cmd.Parameters.Add("@operator_m", SqlDbType.NVarChar).Value = vm.operator_m;
                    cmd.Parameters.Add("@admin", SqlDbType.NVarChar).Value = vm.admin;
                    cmd.Parameters.Add("@voyage_ref", SqlDbType.NVarChar).Value = vm.voyage_ref;
                    cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = vm.created_date;
                    cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = vm.profit_centre_fullname;
                    cmd.Parameters.Add("@Rate", SqlDbType.Decimal).Value = vm.Rate;
                    cmd.Parameters.Add("@commodity", SqlDbType.NVarChar).Value = vm.commodity;
                    cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = vm.last_updated_date;
                    cmd.Parameters.Add("@charterer_", SqlDbType.NVarChar).Value = vm.charterer_;
                   
                    int found = Convert.ToInt16(cmd.ExecuteScalar());
                    if (found == 0)
                    {
                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vm.vessel_code;
                        cmd.CommandText = VoyageMasterSQL.InsertVoyageMaster();
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void ExecuteVoyageMasterInterface()
        {
            try
            {
                IList vmasterList = new ArrayList();

                m_dataBaseConectivity.InterfaceSqlConnection.Open();

                SqlCommand cmd = new SqlCommand(VoyageMasterSQL.GetAllVoyageMasterOutstandingInterface(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    vmasterList.Add(new VoyageMaster(rd, true));
                }
                rd.Close();
                foreach (VoyageMaster vm in vmasterList)
                {
                    if (vm.voyage_no.ToString().Length >= 8)
                    {
                        UpdateFlag(vm.i_voyage_master_id, "Voyage is Cargo Relet", "2");
                        continue;
                    }
                    ProfitCentre profitCentre = m_profitCentreService.GetProfitCentreMapping(vm.profit_centre_fullname);
                    m_vesselService.FindVesselByName(vm.vessel_name, vm.vessel_code, vm.profit_centre_fullname);
                    Vessel profitCentreVessel = m_vesselService.GetVesselMappingByProfitCentreIDAndVesselName(profitCentre.profit_centre_id, vm.vessel_name.Trim(), profitCentre.client);
                    //vaidation-----------------
                    string alert = "";
                    if (profitCentre.client == "") { alert += string.Format("-[E007] Unable find Client Code for Profit Centre '{0}' \r\n", profitCentre.full_name); }
                    if (profitCentreVessel == null) { alert += string.Format("-[E015] Unable find Vessel '{0}' under Profit Centre '{1} {2}' \r\n", vm.vessel_name.Trim(), profitCentre.full_name, profitCentre.agr_dim_value); }
                    if (profitCentreVessel != null && profitCentreVessel.agr_dim_value == "") { alert += string.Format("-[E010] Unable find Dim_Val Code for Vessel '{0}' \r\n", vm.vessel_name.Trim()); }
                    if (profitCentreVessel != null && profitCentreVessel.agr_rel_value == null) { alert += string.Format("-[E010] Unable find CostCentre Code for Vessel '{0}' \r\n", vm.vessel_name.Trim()); }
                    if (alert != "")
                    {
                        UpdateFlag(vm.i_voyage_master_id, alert, "0");
                        //m_alertService.CheckAndUpdateAlert(vm.i_voyage_master_id.ToString(), AlertType.SOFTMAR_VOYAGE_MASTER, AlertStatus.PENDING,
                        //    string.Format("Failed while interfacing Voyage No : {0}, Vessel : {1}, Profit Centre :{2} \r\n", vm.voyage_no, vm.vessel_name.Trim(), vm.profit_centre_fullname) + alert, "Voyage Master");
                        continue;
                    }
                    //  else
                    //   {
                    //       m_alertService.CheckAndUpdateAlert(vm.i_voyage_master_id.ToString(), AlertType.SOFTMAR_VOYAGE_MASTER, AlertStatus.RESOLVED,
                    //          string.Format("Failed while interfacing Voyage No : {0}, Vessel : {1}, Profit Centre :{2} \r\n", vm.voyage_no, vm.vessel_name.Trim(), vm.profit_centre_fullname) + alert, "Voyage Master");
                    //  }
                    //--------------------------
                    ud_voyageimp udv = new ud_voyageimp(profitCentre.client, profitCentreVessel.agr_dim_value, vm);
                    //ud_voyageimp udvExist = m_ud_voyageimp_Service.Find(udv);
                    // if(udvExist!=null)
                    //     m_ud_voyageimp_Service.Update(udv);
                    //  else
                    m_ud_voyageimp_Service.Insert(udv);
                    m_ud_voyageimp_Service.InsertIntoMiddleLayer(udv); //insert as history
                    UpdateFlag(vm.i_voyage_master_id, udv.recid.ToString(), "1");
                }
            }

            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateFlag(int id, string remarks, string pickup_flag)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
                SqlCommand cmd = new SqlCommand(VoyageMasterSQL.UpdateFlag(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = pickup_flag;
                cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@pickup_update_date", SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@remarks", SqlDbType.NVarChar).Value = remarks;
                cmd.Parameters.Add("@i_voyage_master_id", SqlDbType.Int).Value = id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
        }

        public IList GetAllPendingVoyageMaster(string p)
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = VoyageMasterSQL.GetAllPendingVoyageMaster(p);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new VoyageMaster(rd));
                }
                rd.Close();
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList GetAllPendingVoyageMaster()
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = VoyageMasterSQL.GetAllPendingVoyageMaster();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new VoyageMaster(rd));
                }
                rd.Close();
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public int GetAllPendingVoyageMasterCount()
        {
            int commissionInvoices = 0;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = VoyageMasterSQL.GetAllPendingVoyageMasterCount();
                commissionInvoices = Convert.ToInt32(cmd.ExecuteScalar());
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList GetVoyageMasterByVesselNameAndVoyageAndProfitCentre(string vesselName, string voyage, string profit_centre_fullname)
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = VoyageMasterSQL.GetVoyageMasterByVesselNameAndVoyageAndProfitCentre();
                cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselName;
                cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = voyage;
                cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = profit_centre_fullname;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new VoyageMaster(rd,true));
                }
                rd.Close();
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public int GetSoftmarVoyageMasterByVesselNameAndVoyage(string p, string voyage)
        {
            try
            {
                IList vms = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = VoyageMasterSQL.GetAllVoyageMasterByVesselNameVoyage();
                cmd.Parameters.Add("@VesselName", SqlDbType.NVarChar).Value = p;
                cmd.Parameters.Add("@VoyageNo", SqlDbType.NVarChar).Value = voyage;
                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    VoyageMaster po = new VoyageMaster(rd, false);
                    vms.Add(po);
                }
                rd.Close();
                
                foreach (VoyageMaster vm in vms)
                {
                    if (vm.voyage_no.ToString().Length >= 8) continue;// example : 20130019
                    cmd.CommandText = VoyageMasterSQL.CountVoyageMasterTemp();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vm.vessel_name;
                    cmd.Parameters.Add("@dept_fullname", SqlDbType.NVarChar).Value = vm.dept_fullname;
                    cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = vm.voyage_no;
                    cmd.Parameters.Add("@charterer", SqlDbType.NVarChar).Value = vm.charterer;
                    cmd.Parameters.Add("@head_fixture_type", SqlDbType.NVarChar).Value = vm.head_fixture_type;
                    cmd.Parameters.Add("@cp_date", SqlDbType.DateTime).Value = vm.cp_date;
                    cmd.Parameters.Add("@estimated_start_date", SqlDbType.DateTime).Value = vm.estimated_start_date;
                    cmd.Parameters.Add("@start_ballast_date", SqlDbType.DateTime).Value = vm.start_ballast_date;
                    cmd.Parameters.Add("@start_voyage_delivery_date", SqlDbType.DateTime).Value = vm.start_voyage_delivery_date;
                    cmd.Parameters.Add("@end_voyage_delivery_date", SqlDbType.DateTime).Value = vm.end_voyage_delivery_date;
                    cmd.Parameters.Add("@days_total_on_hire", SqlDbType.Decimal).Value = vm.days_total_on_hire;
                    cmd.Parameters.Add("@days_total_off_hire", SqlDbType.Decimal).Value = vm.days_total_off_hire;
                    cmd.Parameters.Add("@tce", SqlDbType.Decimal).Value = vm.tce;
                    cmd.Parameters.Add("@commodity_type", SqlDbType.NVarChar).Value = vm.commodity_type;
                    cmd.Parameters.Add("@cargo_quantity", SqlDbType.Decimal).Value = vm.cargo_quantity;
                    cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = vm.status;
                    cmd.Parameters.Add("@head_fixture_number", SqlDbType.Int).Value = vm.head_fixture_number;
                    cmd.Parameters.Add("@fixture_ref", SqlDbType.NVarChar).Value = vm.fixture_ref;
                    cmd.Parameters.Add("@head_fixture_ref", SqlDbType.NVarChar).Value = vm.head_fixture_ref;
                    cmd.Parameters.Add("@freight_rate_basis", SqlDbType.NVarChar).Value = vm.freight_rate_basis;
                    cmd.Parameters.Add("@operator_m", SqlDbType.NVarChar).Value = vm.operator_m;
                    cmd.Parameters.Add("@admin", SqlDbType.NVarChar).Value = vm.admin;
                    cmd.Parameters.Add("@voyage_ref", SqlDbType.NVarChar).Value = vm.voyage_ref;
                    cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = vm.created_date;
                    cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = vm.profit_centre_fullname;
                    cmd.Parameters.Add("@Rate", SqlDbType.Decimal).Value = vm.Rate;
                    cmd.Parameters.Add("@commodity", SqlDbType.NVarChar).Value = vm.commodity;
                    cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = vm.last_updated_date;
                    cmd.Parameters.Add("@charterer_", SqlDbType.NVarChar).Value = vm.charterer_;
                    int found = Convert.ToInt16(cmd.ExecuteScalar());
                    if (found == 0)
                    {
                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.CommandText = VoyageMasterSQL.InsertVoyageMaster();
                        cmd.ExecuteNonQuery();
                    }
                }
                return vms.Count;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateVesselCode()
        {
            IList voyageMasters = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = "select * from i_voyage_master where pickup_flag = 0";
                SqlCommand cmdSoftmar = new SqlCommand();
                cmdSoftmar.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    voyageMasters.Add(new VoyageMaster(rd, true));
                }
                rd.Close();
                foreach (VoyageMaster st in voyageMasters)
                {
                    cmdSoftmar.CommandText = string.Format(@"Select distinct vVMVoyageSrch.Vessel vessel_code
                     FROM VVMVOYAGESRCH 
					 where vVMVoyageSrch.VesselName = '{0}' and vVMVoyageSrch.VoyageNo = {1}", st.vessel_name, st.voyage_no);
                    string vessel_code = "";
                    SqlDataReader rdSf = cmdSoftmar.ExecuteReader();
                    while (rdSf.Read())
                    {
                        vessel_code = rdSf[0].ToString();
                    }
                    rdSf.Close();
                    cmd.CommandText = string.Format("update i_voyage_master set vessel_code = '{0}'  where i_voyage_master_id = {1}", vessel_code, st.i_voyage_master_id);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
        }
    }
}
